Concurrency control within systems that allow multiple users simultaneous access to shared objects, data records, etc. is an important feature of any server-based product for managing shared data items. In particular, within enterprise resource planning systems there is often a need to support non-serializable cooperation among users having long-lived data transactions.
Generally, pessimistic concurrency control does not block other uses for read operations. For example, a repeatable read makes sure that the read row(s) is not updated, updated for the duration of the data transaction. However, in read operations with the intention of updating the read row, pessimistic concurrency control places an exclusive or update lock on a data item for the duration of a data transaction, thereby preventing other users from reading the data item with the intent to update. As a result, the other users must wait for the lock to be released before reading the data item with the intent to update, which impacts the concurrency and scalability of the system. In some cases, the scope of the lock applies to the entire database, an entire table within the database or several rows within a table rather than just the single row containing the data item being read or updated. As a result, the scope of the lock prevents multiple simultaneous users from reading or updating data items within different rows and/or tables. Further, within balanced tree data structures, queries, such as SQL queries, are unable to start the scan at a precise location. As part of the query execution, rows are scanned and filters are applied during the evaluation of the query. As a result, simultaneous readers prevent each other from reading the data items even when their final query results do not intersect. Although an application may select rows and apply filters to discard selected rows based on the filter criteria, the locks that are acquired on the selected rows continue to exist for the duration of the data transaction. Thus, concurrent tasks may become serialized for long-lived data transactions involving shared tables, even when there is no intersection within the final set resulting from the query.
Optimistic concurrency control allows a user to read, update and delete a data item without preventing other users from doing the same. Optimistic concurrency control assumes that the probability of updating or deleting the same data item during a write operation is small, and read operations are unrestricted. However, in the event that multiple data transactions are updating the same data item during a write operation, updates may be lost and only last update is maintained between the concurrent users, thereby causing data inconsistency. In other words, a first user may ultimately update a data item within a row of the table based on the originally retrieved values which were subsequently changed by a concurrent user. As a result, the update is based on stale data.